package cn.lfzj.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.lfzj.model.Pub;

public class PubDao {
	private Connection conn;

	private void getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8";
			conn = DriverManager.getConnection(url, "root", "jisuanjixi");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void close() {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<Pub> selectAll() {
		List<Pub> list = new ArrayList<Pub>();
		this.getConnection();
		try {
			String sql = "select * from pubs";
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			Pub pub = null;
			while (rs.next()) {
				pub = new Pub();
				pub.setId(rs.getInt("id"));
				pub.setName(rs.getString("name"));
				list.add(pub);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		this.close();
		return list;
	}

	public int add(String name) {
		int result = 0;
		this.getConnection();
		try {
			String sql = "insert into pubs(name) values(?)";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			result = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		this.close();
		return result;
	}

	public int deleteById(int id) {
		int result = 0;
		this.getConnection();
		try {
			String sql = "delete from pubs where id=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		this.close();
		return result;
	}
}
